﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Windows.Forms;

namespace GetData
{
    public class GetExcelData
    {

        #region 获取excel方法
        /// <summary>
        /// 获取excel数据
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <param name="workcode">第几个工作表</param>
        /// <param name="startcell">开始单元格</param>
        /// <param name="endcell">结束单元格</param>
        /// <returns></returns>
        public DataTable bind(string fileName,string workname_, string startcell,string endcell)//获取excel方法
        {
            DataTable M_dt = new DataTable();
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + "Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'";
            string tablename = GetExcelFirstTableName(fileName, workname_);
            string sql = "SELECT *  FROM [" + tablename +startcell+":"+ endcell + "]";
            sql = sql.Replace("'", "");
            sql = sql.Replace("_FilterDatabase", "");
            OleDbDataAdapter da = new OleDbDataAdapter(sql, strConn);
            try
            {
                M_dt.Clear();
                da.Fill(M_dt);
                if (M_dt.Rows.Count>0)
                {
                    removeEmpty(M_dt);
                    if (string.IsNullOrEmpty(M_dt.Rows[0][0].ToString()))
                    {
                        M_dt.Rows.Remove(M_dt.Rows[0]);
                    }
                }
                else
                {
                    MessageBox.Show("导入的表没有数据");
                }
            }
            catch (Exception err)
            {
                CsLogFun.WriteToLog(err);
                throw new Exception("提示：" + err.Message);
            }
           // M_dt.Columns.Add("当前选择",typeof(bool)).SetOrdinal(0);
            return M_dt;
        }
        #endregion

        #region 获取excel第一个表名
        /// <summary>
        /// C#中获取Excel文件的第一个表名 
        /// Excel文件中第一个表名的缺省值是Sheet1$, 但有时也会被改变为其他名字. 如果需要在C#中使用OleDb读写Excel文件, 就需要知道这个名字是什么. 以下代码就是实现这个功能的:
        /// </summary>
        /// <param name="excelFileName">文件路径名</param>
        /// <param name="workcode">工作表序号</param>
        /// <returns></returns>
        public string GetExcelFirstTableName(string excelFileName,string workanme_)
        {
            string tableName = null;
            try
            {
                if (File.Exists(excelFileName))
                {
                    using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFileName + ";" + "Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'"))
                    {
                        conn.Open();
                        DataTable M_dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        //tableName = M_dt.Rows[0][workcode].ToString().Trim();
                        for (int i = 0; i < M_dt.Rows.Count; i++)//循环获取所有表名
                        {
                            for (int j = 0; j <= 3; j++)
                            {
                                tableName = M_dt.Rows[i][j].ToString().Trim();
                                if (tableName.Contains(workanme_))
                                {
                                    return tableName;
                                }
                            }

                        }
                    }
                }

            }
            catch (Exception err)
            {
                CsLogFun.WriteToLog(err);
                throw new Exception("提示：" + err.Message);
            }
            return tableName;
        }
        #endregion
        /// <summary>
        /// 循环去除datatable中的空行
        /// </summary>
        /// <param name="M_dt"></param>
        protected void removeEmpty(DataTable M_dt)
        {
            try
            {
                List<DataRow> removelist = new List<DataRow>();
                for (int i = 0; i < M_dt.Rows.Count; i++)
                {
                    bool rowdataisnull = true;
                    for (int j = 0; j < M_dt.Columns.Count; j++)
                    {

                        if (!string.IsNullOrEmpty(M_dt.Rows[i][j].ToString().Trim()))
                        {

                            rowdataisnull = false;
                        }

                    }
                    if (rowdataisnull)
                    {
                        removelist.Add(M_dt.Rows[i]);
                    }

                }
                for (int i = 0; i < removelist.Count; i++)
                {
                    M_dt.Rows.Remove(removelist[i]);
                }
            }
            catch (Exception err)
            {

                CsLogFun.WriteToLog(err);
                throw new Exception("提示：" + err.Message);
            }

        }
    }

}
